package com.anda.logistics.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import com.anda.logistics.dao.SystemCodeDao;
import com.anda.logistics.model.GoogleRegionCode;
import com.anda.logistics.model.SystemCode;
import com.anda.logistics.util.JQGridPager;
import com.anda.logistics.util.JQGridPager.SearchFilter;
import com.anda.logistics.util.JQGridPager.SearchFilterRules;
import com.anda.logistics.vo.RegionInfoVo;

@Component
public class SystemCodeDaoImpl extends AndaBaseSuperDao implements SystemCodeDao{
	
	@Override
	public void save(SystemCode systemCode) {
		sessionFactory.getCurrentSession().save(systemCode);
	}

	@Override
	public List<SystemCode> getAll() {
		return sessionFactory.getCurrentSession().createQuery("from SystemCode order by id").list();
	}
	
	@Override
	public SystemCode getOne(long id){
		return (SystemCode) sessionFactory.getCurrentSession().get(SystemCode.class, id);
	}
	
	@Override
	public void deleteOne(long id){
		SystemCode s = new SystemCode(id);
		sessionFactory.getCurrentSession().delete(s);
	}

	@Override
	public void update(SystemCode systemCode) {
		sessionFactory.getCurrentSession().update(systemCode);		
	}
	
	private String getPagedSql(JQGridPager pager){
		StringBuffer queryString = new StringBuffer();
		queryString.append("from SystemCode ");
		
		SearchFilter filters = pager.getFiltersObj();
		if ( filters != null){
			SearchFilterRules[] rules = filters.getRules();
			for(int i=0; i< rules.length; i++){
				if (i==0){
					queryString.append(" where ");
				} else {
					queryString.append(filters.getGroupOp());
				}
				if (rules[i].getField().endsWith("Time")){
					queryString.append(" DATE(").append(rules[i].getField()).append(")").append(rules[i].getMapedOp()) .append(" ? ");
				} else {
					queryString.append(" ").append(rules[i].getField()).append(rules[i].getMapedOp()) .append(" ? ");
				}
			}
		}
		//queryString.append(" order by codeName,parentValue,sortNum,codeValue ");
		return queryString.toString();
	}
	
	private void setPagedSql(Query q,JQGridPager pager){
		SearchFilter filters = pager.getFiltersObj();
		if ( filters != null){
			SearchFilterRules[] rules = filters.getRules();
			for(int i=0; i< rules.length; i++){
				q.setString(i, rules[i].getMapedData(false));
			}
		}
	}
	
	@Override
	public List<SystemCode> getPagerList(JQGridPager pager) {
		//this.hibernateTemplate.findByCriteria(criteria, (pager.getPage() -1) * pager.getRows(), pager.getRows());
		
		StringBuffer queryString = new StringBuffer();
		queryString.append(getPagedSql(pager));
		
		queryString.append(" order by ");
		if(pager.getSidx() != null && pager.getSidx().trim().length() > 0){
			queryString.append(pager.getSidx()).append(" ").append(pager.getSord()).append(",");
		}
		queryString.append(" codeName,parentValue,sortNum,codeValue ");
		
		Session session = sessionFactory.getCurrentSession();
		Query q = session.createQuery(queryString.toString());
		
		setPagedSql(q,pager);
		
		q.setFirstResult((pager.getPage() -1) * pager.getRows());
		q.setMaxResults(pager.getRows());
		return q.list();
		//return null;
	}
	@Override
	public long getPagedRecords(JQGridPager pager){
		StringBuffer queryString = new StringBuffer();
		queryString.append("select count(*) ");
		queryString.append(getPagedSql(pager));
		Query q = sessionFactory.getCurrentSession()
				.createQuery(queryString.toString());
		setPagedSql(q,pager);
		return (Long) ( q.uniqueResult());
	}
	
	@Override
	public long getRecords() {
		//return this.hibernateTemplate.find("select count(*) from TruckInfo t ").get(0);	
		return (Long) ( sessionFactory.getCurrentSession().createQuery("select count(*) from SystemCode t ").uniqueResult());
	}
	
	@Override
	public Map<String, List<SystemCode>> getAllPartedCode(){
		List<SystemCode> list = sessionFactory.getCurrentSession()
				.createQuery("from SystemCode order by codeName,sortNum,codeValue")
				.list();
		Map<String, List<SystemCode>> mapCode = new HashMap<String,List<SystemCode>>();
		String codeName = null;
		List<SystemCode> oneCode = new ArrayList<SystemCode>();
		for(SystemCode code: list){
			if (code.getParentValue() != null && code.getParentValue().trim().length() > 0) continue;
			if (codeName != null && !codeName.equalsIgnoreCase(code.getCodeName())){
				mapCode.put(codeName, oneCode);
				oneCode = new ArrayList<SystemCode>();
			}
			codeName = code.getCodeName();
			oneCode.add(code);
		}
		mapCode.put(codeName, oneCode);
		return mapCode;
	}
	
	@Override
	public List<RegionInfoVo> getAllRegionInfo(){
		StringBuffer sql = new StringBuffer();
		sql .append("select p.codeValue provinceCode,p.codeText provinceName,c.codeValue cityCode,c.CodeText cityName,r.codeValue regionCode,r.codeText regionName ")
			.append("  from ")
			.append("( ")
			.append("select *  ")
			.append("  from anda.systemcode ")
			.append(" where codeName = 'regioncode' ")
			.append("   and (parentValue is null or trim(parentValue) = '') ")
			.append(") p ")
			.append("left join ")
			.append("( ")
			.append("select *  ")
			.append("  from anda.systemcode ")
			.append(" where codeName = 'regioncode' ")
			.append("   and parentValue in  ")
			.append("(select codeValue ")
			.append("  from anda.systemcode ")
			.append(" where codeName = 'regioncode' ")
			.append("   and (parentValue is null or trim(parentValue) = '') ")
			.append(") ")
			.append(") c on p.codeValue = c.parentValue ")
			.append("left join  ")
			.append("( ")
			.append("select *  ")
			.append("  from anda.systemcode ")
			.append(" where codeName = 'regioncode' ")
			.append("   and parentValue in  ")
			.append("( ")
			.append("select codeValue ")
			.append("  from anda.systemcode ")
			.append(" where codeName = 'regioncode' ")
			.append("   and parentValue in  ")
			.append("(select codeValue ")
			.append("  from anda.systemcode ")
			.append(" where codeName = 'regioncode' ")
			.append("   and (parentValue is null or trim(parentValue) = '') ")
			.append(") ")
			.append(") ")
			.append(") r on c.codeValue = r.parentValue ")
			.append("order by p.sortnum, c.sortnum, r.sortnum "); 
		return sessionFactory.getCurrentSession()
			.createSQLQuery(sql.toString()).setResultTransformer(Transformers.aliasToBean(RegionInfoVo.class)).list();
	}
	
	@Override
	public List<SystemCode> getProvinceCode(){
		return sessionFactory.getCurrentSession()
			.createQuery("from SystemCode where codeName = ? and parentValue is null order by sortnum,codeValue")
			.setString(0, "regioncode")
			.list();
	}
	
	@Override
	public List<SystemCode> getRegionChildren(String parentValue){
		return sessionFactory.getCurrentSession()
			.createQuery("from SystemCode where codeName = ? and parentValue = ? order by sortnum,codeValue")
			.setString(0,  "regioncode")
			.setString(1, parentValue)
			.list();
	}

	@Override
	public List<String> getAllCodeNames() {
		return sessionFactory.getCurrentSession()
			.createSQLQuery("select distinct codeName from SystemCode order by codeName ")
			.setResultTransformer(Transformers.TO_LIST)
			.list();
	}
	
	@Override
	public List<SystemCode> getAllOtherCode(){
		return
				sessionFactory.getCurrentSession()
				.createQuery("from SystemCode where codeName <> 'regioncode' order by codeName,sortNum,codeValue ")
				.list();
	}

	@Override
	public List<GoogleRegionCode> getGoogleRegionCode() {
		return
				sessionFactory.getCurrentSession()
					.createQuery("from GoogleRegionCode order by pid,rownum,cid ")
					.list();
	}
}
